[Mysql] mysql-binary排序规则与_bin排序规则对比

1、binary排序规则与_bin排序规则对比

二进制字符串（像用binary，barbinary，blob存储的数据类型）有一个字符集和以binary命名的排序规则。二进制字符串是序列字节，这些字节的数字值决定了比较和排序的顺序。
非二进制字符串（像用char，varchar，text存储的数据类型）有一个字符集和排序规则（名称不是binary）。一个给定的非二进制字符集有几个排序规则，每一种排序规则对集合中的字符串定义一个特定的比较和排序的顺序。这些非二进制字符集的命名规则是在二进制字符集排序规则的名称后面添加_bin后缀。例如二进制排序规则latin1和utf8分别被命名latin1_bin,utf8_bin。

2、在某些方面，二进制排序规则与_bin排序规则不同。

2.1、比较和排序单元：

二进制字符串是字节序列。对于二进制排序规则，比较和排序是基于数字字节值。
非二进制字符串是（可能是多字节）字符的序列。非二进制排序规则定义了比较和排序字符值的顺序。对于_bin排序规则，这个顺序基于数字字符串编码值，类似于二进制字符串顺序（多字节字符串编制值除外）

2.2、字符转换：

一个非二进制字符串有一个字符集，在很多情况下（即使字符串有_bin排序规则）也可以自动转换为另一个字符集。

当从另一个有不同字符集的列分配列值：
UPDATE t1 SET utf8_bin_column=latin1_column;
INSERT INTO t1 (latin1_column) SELECT utf8_bin_column FROM t2;
当使用字符串文字为insert或update分配值时：
SET NAMES latin1;
INSERT INTO t1 (utf8_bin_column) VALUES ('string-in-latin1');
当从服务端给客户端返回结果：
SET NAMES latin1;
SELECT utf8_bin_column FROM t2;

对于二进制字符串列，没有转换发生。对于前面的情况，字符串值是按字节复制的。

2.3、大小写转换：

非二进制字符集排序规则提供关于字符字母大小写的信息，因此非二进制字符可以被转换从一个字母到另一个，即使_bin排序规则忽略字母大小写顺序：

mysql> SET NAMES latin1 COLLATE latin1_bin;
mysql> SELECT LOWER('aA'), UPPER('zZ');
+-------------+-------------+
| LOWER('aA') | UPPER('zZ') |
+-------------+-------------+
| aa          | ZZ          |
+-------------+-------------+

字母大小写的概念对二进制字符串的字节不适用，执行字母大小写转换，字符串必须转换为非二进制字符串

mysql> SET NAMES binary;
mysql> SELECT LOWER('aA'), LOWER(CONVERT('aA' USING latin1));
+-------------+-----------------------------------+
| LOWER('aA') | LOWER(CONVERT('aA' USING latin1)) |
+-------------+-----------------------------------+
| aA          | aa                                |
+-------------+-----------------------------------+

2.4、比较过程的尾随空间处理：非二进制字符串为所有的排序规则进行填充空间，包括_bin排序规则，尾随空间在比较过程是无关紧要的：

mysql> SET NAMES utf8 COLLATE utf8_bin;
mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          1 |
+------------+

对于二进制字符串，比较过程所有的字符都是重要的，包括尾随空间:

mysql> SET NAMES binary;
mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          0 |
+------------+

2.5、在插入和检索中的尾随空间处理：

char(n)列存储非二进制字符串，当insert操作是，值小于n字符会使用空格扩展。在检索时尾随空间会被移除。
二进制列存储二进制字符串,插入过程中值小于n字节，会使用ox00扩展，在检索过程中不会被移除；总是返回声明长度的值。

mysql> CREATE TABLE t1 (
         a CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin,
         b BINARY(10)
       );
mysql> INSERT INTO t1 VALUES ('a','a');
mysql> SELECT HEX(a), HEX(b) FROM t1;
+--------+----------------------+
| HEX(a) | HEX(b)               |
+--------+----------------------+
| 61     | 61000000000000000000 |
+--------+----------------------+